import { Injectable } from '@nestjs/common';
import { UpdateUserDto } from './dto/update-user.dto';
import { User } from './entities/user.entity';
import { InjectRepository } from '@nestjs/typeorm';
import { In, IsNull, Not, Repository } from 'typeorm';
import { CreateUserDto } from './dto/create-user.dto';
import * as XLSX from 'xlsx';

@Injectable()
export class UserService {
  private tableName = 'user';

  constructor(
    @InjectRepository(User) private readonly userRepository: Repository<User>,
  ) {}

  findOne(updateUserDto: UpdateUserDto) {
    return this.userRepository.findOne({
      where: {
        number: updateUserDto.number,
      },
    });
  }

  create(createUserDto: CreateUserDto) {
    const user = this.userRepository.create(createUserDto);
    return this.userRepository.save(user);
  }

  update(updateUserDto: UpdateUserDto) {
    return this.userRepository.update(updateUserDto.id, updateUserDto);
  }

  findRandomList(count: number) {
    return this.userRepository
      .createQueryBuilder('user')
      .where('raceUrl IS NOT NULL')
      .orderBy('RAND()', 'DESC')
      .limit(count)
      .getMany();
  }

  getStats() {
    return this.userRepository.query(`
      SELECT 
        company,
        SUM(CASE WHEN status IN (0, 1) THEN 1 ELSE 0 END) AS totalCount,
        SUM(CASE WHEN status = 1 AND raceUrl IS NOT NULL THEN 1 ELSE 0 END) AS appliedCount,
        SUM(CASE WHEN status  IN (1, 2) AND raceUrl IS NOT NULL THEN 1 ELSE 0 END) AS realCount,
        (SUM(CASE WHEN status = 1 AND raceUrl IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*)) AS ratio
      FROM 
       ${this.tableName}
      GROUP BY 
        company
      ORDER BY
        ratio DESC,
        totalCount DESC;
    `);
  }

  async exportDataToExcel(date?: string) {
    const data: User[] = await this.userRepository.query(`
      SELECT 
        *
      FROM
        ${this.tableName} 
      WHERE 
        status IN (1, 2)  
        ${date ? `AND DATE(updatedRaceAt) = '${date}'` : ''}
        ORDER BY 
        updatedRaceAt DESC;`);

    const rows = data.map((item) => {
      return {
        分公司: item.company,
        姓名: item.name,
        工号: item.number,
        比武组别: item.group,
        比武类别: item.category,
        参赛作品名称: item.works,
        个人海报图片地址: item.personalUrl,
        个人海报上传时间: item.updatedPersonalAt,
        比武照片图片地址: item.raceUrl,
        比武照片上传时间: item.updatedRaceAt,
        类型: item.status === 1 ? '报名' : '打卡',
      };
    });

    const worksheet = XLSX.utils.json_to_sheet(rows);
    const workbook = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(workbook, worksheet, '春蕾杯比武大赛数据');
    const filename = `春蕾杯比武大赛数据-${date || '全部'}.xlsx`;
    await XLSX.writeFile(workbook, filename);
    return filename;
  }

  async findApplyList(pageNo: number, pageSize: number) {
    return this.userRepository.findAndCount({
      where: {
        status: In([1, 2]),
        raceUrl: Not(IsNull()),
      },
      order: {
        updatedRaceAt: 'DESC',
      },
      skip: (pageNo - 1) * pageSize,
      take: pageSize,
    });
  }

  async deleteRaceUrl(id: string) {
    return this.userRepository.update(id, {
      raceUrl: null,
      updatedRaceAt: null,
    });
  }
}
